﻿Imports System.IO
Imports System.Web
Imports System.Data
Imports System.Text
Imports System.Web.Services
Imports System.Data.SqlClient
Imports System.Web.Services.Protocols
Imports System.Configuration.ConfigurationManager

' Para permitir que se llame a este servicio web desde un script, usando ASP.NET AJAX, quite la marca de comentario de la siguiente línea.
' <System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class Service
     Inherits System.Web.Services.WebService

#Region "Variables"
    Private Declare Auto Function SetProcessWorkingSetSize Lib "kernel32.dll" (ByVal procHandle As IntPtr, ByVal min As Int32, ByVal max As Int32) As Boolean
    Dim resultado As Boolean
#End Region


#Region "WEBMETHOD"
    ''' <summary>
    ''' Ejecuta un Procedimiento Almacenado Enviando con un parametro Standard de tipo XML
    ''' </summary>
    ''' <param name="StrKeyConn">Nombre de la cadena de conexión</param>
    ''' <param name="StrXMLDatos">XML con los parametros</param>
    ''' <param name="sp">Procedimiento almacenado a ejecutar</param>
    ''' <returns>dataset con los resultados del Procedimiento almacenado</returns>
    ''' <remarks></remarks>
    <WebMethod()> Public Function EjecutarConParametroXML(ByVal StrKeyConn As String, ByVal StrXMLDatos As String, ByVal sp As String) As DataSet
        Dim dsResultado As New DataSet
        Try
            dsResultado = ProcesarSPConXML(sp, StrXMLDatos, StrKeyConn)
            Return dsResultado
        Catch ex As Exception
            Return dsResultado
        Finally
            dsResultado.Dispose()
            dsResultado = Nothing
            Call ClearMemory()
        End Try
    End Function

    ''' <summary>
    ''' Ejecuta un Procedimiento Almacenado sin parametro
    ''' </summary>
    ''' <param name="StrKeyConn">Nombre de la cadena de conexión</param>
    ''' <param name="SPEjecutar">Procedimiento almacenado a ejecutar</param>
    ''' <returns>dataset con los resultados del Procedimiento almacenado</returns>
    ''' <remarks></remarks>
    <WebMethod()> Public Function EjecutarSinParametros(ByVal StrKeyConn As String, ByVal SPEjecutar As String) As DataSet
        Dim dsResultado As New DataSet
        Try
            dsResultado = ProcesarSP(SPEjecutar, StrKeyConn)
            Return dsResultado
        Catch ex As Exception
            Return dsResultado
        Finally
            dsResultado.Dispose()
            dsResultado = Nothing
            Call ClearMemory()
        End Try
    End Function

#End Region

#Region "Funciones Varias"

    Private Function ProcesarSPConXML(ByVal SPEjecutar As String, ByVal DatosXML As String, ByVal strKeyConexion As String) As DataSet
        Dim miDS As New DataSet
        Dim myConnection As SqlConnection
        Dim myDataAdapter As SqlDataAdapter
        Dim myDataset As DataSet
        Try
            Dim ConnectionString As String

            ConnectionString = ConfigurationManager.ConnectionStrings(strKeyConexion).ConnectionString
            myConnection = New SqlConnection(ConnectionString)         
            myDataAdapter = New SqlDataAdapter(SPEjecutar, myConnection)
            myDataAdapter.SelectCommand.CommandTimeout = System.Configuration.ConfigurationManager.AppSettings("SegundosCommandTimeOut")
            myDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            myDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@StrXMLDatos", SqlDbType.Xml))
            myDataAdapter.SelectCommand.Parameters("@StrXMLDatos").Value = DatosXML
            myDataset = New DataSet
            myDataAdapter.SelectCommand.CommandTimeout = System.Configuration.ConfigurationManager.AppSettings("SegundosCommandTimeOut")
            myDataAdapter.Fill(myDataset, "resultado")
            myConnection.Close()
            miDS = myDataset
        Catch objException As Exception
            Dim myError As Comunes.Parametros = New Comunes.Parametros
            Dim myString As String
            Dim myStringBuilder As StringBuilder
            Dim myStringReader As StringReader
            myError.Agregar("ErrorNumber", "-2")
            myError.Agregar("ErrorMessage", objException.Message)
            myError.Agregar("src_error", objException.Source)
            myError.Agregar("tgt_error", objException.TargetSite.Name)
            myError.Agregar("ws_error", "Service.asmx")
            myError.Agregar("met_error", "EjecutarXML " & SPEjecutar)
            myString = myError.GeneraXML("error").InnerXml
            myStringBuilder = New StringBuilder(myString)
            myStringReader = New StringReader(myStringBuilder.ToString())
            miDS.ReadXml(myStringReader)
            myError = Nothing
            myStringBuilder = Nothing
            myStringReader.Dispose()
            myStringReader = Nothing
        Finally
            myConnection = Nothing
            myDataAdapter = Nothing
            myDataset = Nothing
        End Try

        Return miDS
    End Function

    Private Function ProcesarSP(ByVal SPEjecutar As String, ByVal strKeyConexion As String) As DataSet
        Dim miDS As New DataSet
        Dim myConnection As SqlConnection
        Dim myDataAdapter As SqlDataAdapter
        Dim myDataset As DataSet
        Try
            Dim ConnectionString As String

            ConnectionString = ConfigurationManager.ConnectionStrings(strKeyConexion).ConnectionString
            myConnection = New SqlConnection(ConnectionString)
            myDataAdapter = New SqlDataAdapter(SPEjecutar, myConnection)
            myDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            myDataset = New DataSet
            myDataAdapter.SelectCommand.CommandTimeout = System.Configuration.ConfigurationManager.AppSettings("SegundosCommandTimeOut")
            myDataAdapter.Fill(myDataset, "resultado")
            myConnection.Close()
            myConnection = Nothing
            miDS = myDataset
        Catch objException As Exception
            Dim myError As Comunes.Parametros = New Comunes.Parametros
            Dim myString As String
            Dim myStringBuilder As StringBuilder
            Dim myStringReader As StringReader

            myError.Agregar("ErrorNumber", "-2")
            myError.Agregar("ErrorMessage", objException.Message)
            myError.Agregar("src_error", objException.Source)
            myError.Agregar("tgt_error", objException.TargetSite.Name)
            myError.Agregar("ws_error", "Service.asmx")
            myError.Agregar("met_error", "EjecutarXML " & SPEjecutar)
            myString = myError.GeneraXML("error").InnerXml
            myStringBuilder = New StringBuilder(myString)
            myStringReader = New StringReader(myStringBuilder.ToString())
            miDS.ReadXml(myStringReader)
            myError = Nothing
            myStringBuilder = Nothing
            myStringReader.Dispose()
            myStringReader = Nothing
        Finally
            myConnection = Nothing
            myDataAdapter = Nothing
            myDataset = Nothing
        End Try

        Return miDS
    End Function

    Private Sub ClearMemory()
        Try
            Dim Mem As System.Diagnostics.Process
            Mem = System.Diagnostics.Process.GetCurrentProcess()
            SetProcessWorkingSetSize(Mem.Handle, -1, -1)
        Catch ex As Exception
        End Try
    End Sub

#End Region

End Class
